﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace cls.u_Models
{
    public partial class BackorderUserMgr : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Label_UserName.Text = User.Identity.Name + "：";

            if (!Page.IsPostBack)
            {
                ListBoxShowUsers();
                ListBoxShowOrgs();
            }            
        }

        protected void ListBoxShowOrgIncUsers(String OrgId)
        {
            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            String selectCommandString = "SELECT [UserId],[UserName] FROM [dbo].[u_cls_UserInOrg] WHERE [OrgId] IN (SELECT Id FROM [dbo].[u_cls_Org] WHERE Id = @Id) ORDER BY UserName";
            
            SqlCommand SelectCommand = new SqlCommand(selectCommandString, SqlConnection);

            SelectCommand.Parameters.Add("@Id", SqlDbType.UniqueIdentifier);
            SelectCommand.Parameters["@Id"].Value = new System.Guid(OrgId);

            SqlDataReader OrgIncUsersReader = SelectCommand.ExecuteReader();

            while (OrgIncUsersReader.Read())
            {
                ListBox_OrgIncUsers.Items.Add(new ListItem(OrgIncUsersReader["UserName"].ToString(), OrgIncUsersReader["UserId"].ToString()));
            }
            
            OrgIncUsersReader.Close();
            SelectCommand.Dispose();            
            SqlConnection.Close();
        }

        protected void ListBoxShowUsers()
        {
            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            String selectCommandString = "SELECT [UserId],[UserName] FROM [kpi_aspnet_db].[dbo].[aspnet_Users] WHERE [UserId] NOT IN (SELECT [UserId] FROM [cls_forms_db].[dbo].[u_cls_UserInOrg]) ORDER BY UserName";
            SqlCommand SelectCommand = new SqlCommand(selectCommandString, SqlConnection);
            SqlDataReader ExtUsersReader = SelectCommand.ExecuteReader();

            while (ExtUsersReader.Read())
            {
                ListBox_ExtUsers.Items.Add(new ListItem(ExtUsersReader["UserName"].ToString(), ExtUsersReader["UserId"].ToString()));
            }
                        
            ExtUsersReader.Close();
            SelectCommand.Dispose();
            SqlConnection.Close();
        }

        protected void ListBoxShowOrgs()
        {
            //方法一
            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            String selectCommandString = "SELECT [Id],[OrgCode],[OrgName] FROM [dbo].[u_cls_Org] ORDER BY [OrgCode]";
            SqlCommand SelectCommand = new SqlCommand(selectCommandString, SqlConnection);
            SqlDataReader OrgNamesReader = SelectCommand.ExecuteReader();

            while (OrgNamesReader.Read())
            {
                ListBox_OrgNames.Items.Add(new ListItem(OrgNamesReader["OrgCode"].ToString() + "-" + OrgNamesReader["OrgName"].ToString(), OrgNamesReader["Id"].ToString()));
            }
            ListBox_OrgNames.AppendDataBoundItems = false;
                        
            OrgNamesReader.Close();
            SelectCommand.Dispose();
            SqlConnection.Close();


            //方法二
            //DataSet DataSetOrgNames = new DataSet();

            //SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            //SqlConnection.Open();

            //String SelectCommandString = "SELECT [Id],[OrgCode],[OrgName] FROM [dbo].[u_cls_Org] ORDER BY [OrgCode]";
            
            //SqlDataAdapter DataAdapterOrg = new SqlDataAdapter(SelectCommandString, SqlConnection);
            //DataAdapterOrg.Fill(DataSetOrgNames);

            //ListBox_OrgNames.DataSource = DataSetOrgNames;
            //ListBox_OrgNames.DataValueField = "Id";
            //ListBox_OrgNames.DataTextField = "OrgName";
            //ListBox_OrgNames.AppendDataBoundItems = false;            
            //ListBox_OrgNames.DataBind();

            //DataAdapterOrg.Dispose();
            //SqlConnection.Close();
        }

        //参考资料
        //http://msdn.microsoft.com/zh-cn/library/system.web.ui.webcontrols.listbox_events(v=vs.110).aspx
        //为控件的 SelectedIndexChanged 事件创建一个事件处理程序。默认情况下，SelectedIndexChanged 事件并不立即导致向服务器发送页面，而是当下次发送窗体时在服务器代码中引发此事件。若要使 SelectedIndexChanged 事件引发立即发送，请将控件的 AutoPostBack 属性设置为 true。
        //AutoPostBack = "true" 会引发Item重复添加，必须在Page_Load()方法中使用if (!Page.IsPostBack){}。
        protected void ListBox_OrgNames_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Label_DebugMsg.Text = ListBox_OrgNames.SelectedValue.ToUpper();
            //Label_DebugMsg.Visible = true;
            //无效
            //ListBox_OrgIncUsers.Dispose();

            String OrgId = ListBox_OrgNames.SelectedValue.ToUpper();

            ListBox_OrgIncUsers.Items.Clear();
            ListBoxShowOrgIncUsers(OrgId);

            //避免重复添加OrgName项
            //ListBox_OrgNames.Items.Clear();
            //ListBoxShowOrgs();

            //
            ListBox_ExtUsers.Items.Clear();
            ListBoxShowUsers();
        }        

        protected void Button_AddUserToOrg_Click(object sender, EventArgs e)
        {
            if (ListBox_OrgNames.SelectedValue == null && ListBox_ExtUsers.SelectedItem == null)
            {
                //MessageBox.Show("Item is not available in ListBox2");
            }
            else
            {
                String UserId = ListBox_ExtUsers.SelectedValue.ToUpper();
                String UserName = ListBox_ExtUsers.SelectedItem.ToString();
                String OrgId = ListBox_OrgNames.SelectedValue.ToUpper();

                //参考资料
                //http://msdn.microsoft.com/zh-cn/library/b873y76a(v=vs.110).aspx
                String OrgName = ListBox_OrgNames.SelectedItem.ToString().Split('-')[1].ToString();

                AddUserToOrg(UserId, UserName, OrgId, OrgName);

                //重新显示内容，否则会重复。
                ListBox_OrgNames.Items.Clear();
                ListBoxShowOrgs();
            }
        }

        protected void Button_RemUserFromOrg_Click(object sender, EventArgs e)
        {
            if (ListBox_OrgNames.SelectedValue == null)
            {

            }
            else
            {
                String UserId = ListBox_OrgIncUsers.SelectedValue.ToUpper();
                String OrgId = GetOrgIdByUserId(UserId);

                RemUserFromOrg(UserId, null, OrgId, null);

                //重新显示内容。
                ListBox_OrgNames.Items.Clear();
                ListBoxShowOrgs();

                ListBox_OrgIncUsers.Items.Clear();
                ListBoxShowOrgIncUsers(OrgId);
            }
        }

        protected String GetOrgIdByUserId(String UserId)
        {
            String OrgId = "";

            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            String selectCommandString = "SELECT [OrgId] FROM [dbo].[u_cls_UserInOrg] WHERE [UserId] = @UserId";
            SqlCommand SelectCommand = new SqlCommand(selectCommandString, SqlConnection);

            SelectCommand.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier);
            SelectCommand.Parameters["@UserId"].Value = new System.Guid(UserId);

            SqlDataReader OrgIdReader = SelectCommand.ExecuteReader();

            while (OrgIdReader.Read())
            {
                OrgId = OrgIdReader["OrgId"].ToString().ToUpper();
            }
            
            OrgIdReader.Close();
            SelectCommand.Dispose();
            SqlConnection.Close();

            return OrgId;
        }

        protected void AddUserToOrg(String UserId, String UserName, String OrgId, String OrgName)
        {
            //foreach是为了应对ListBox多选的情况，否则无需。
            foreach (ListItem item in this.ListBox_ExtUsers.Items)
            {
                if (item.Selected)
                {
                    //ExecuteUpdateQuery(item.Value, "add");
                    //ListBox_UserMgr.Items.Insert(0,item);

                    SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
                    SqlConnection.Open();

                    SqlCommand InsertCommand = new SqlCommand("INSERT INTO [dbo].[u_cls_UserInOrg] ([UserId],[UserName],[OrgId],[OrgName]) VALUES (@UserId,@UserName,@OrgId,@OrgName)", SqlConnection);
                    InsertCommand.CommandType = CommandType.Text;

                    InsertCommand.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier);
                    InsertCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 4);
                    InsertCommand.Parameters.Add("@OrgId", SqlDbType.UniqueIdentifier);
                    InsertCommand.Parameters.Add("@OrgName", SqlDbType.NVarChar, 50);

                    InsertCommand.Parameters["@UserId"].Value = new System.Guid(UserId);
                    InsertCommand.Parameters["@UserName"].Value = UserName;
                    InsertCommand.Parameters["@OrgId"].Value = new System.Guid(OrgId);
                    InsertCommand.Parameters["@OrgName"].Value = OrgName;

                    InsertCommand.ExecuteNonQuery();

                    InsertCommand.Dispose();
                    SqlConnection.Close();
                }
            }

            ListBox_ExtUsers.Items.Clear();
            ListBoxShowUsers();
        }

        protected void RemUserFromOrg(String UserId, String UserName, String OrgId, String OrgName)
        {
            //foreach是为了应对ListBox多选的情况，否则无需。
            foreach (ListItem item in this.ListBox_OrgIncUsers.Items)
            {
                if (item.Selected)
                {
                    SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
                    SqlConnection.Open();

                    SqlCommand DeleteCommand = new SqlCommand("DELETE FROM [dbo].[u_cls_UserInOrg] WHERE [UserId] = @UserId AND OrgId = @OrgId", SqlConnection);
                    DeleteCommand.CommandType = CommandType.Text;

                    DeleteCommand.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier);
                    //DeleteCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 4);
                    DeleteCommand.Parameters.Add("@OrgId", SqlDbType.UniqueIdentifier);
                    //DeleteCommand.Parameters.Add("@OrgName", SqlDbType.NVarChar, 50);

                    DeleteCommand.Parameters["@UserId"].Value = new System.Guid(UserId);
                    //DeleteCommand.Parameters["@UserName"].Value = UserName;
                    DeleteCommand.Parameters["@OrgId"].Value = new System.Guid(OrgId);
                    //DeleteCommand.Parameters["@OrgName"].Value = OrgName;

                    DeleteCommand.ExecuteNonQuery();

                    DeleteCommand.Dispose();
                    SqlConnection.Close();
                }
            }

            ListBox_ExtUsers.Items.Clear();
            ListBoxShowUsers();
        }

        protected void Button_Return_Click(object sender, EventArgs e)
        {
            Response.Redirect("../Default.aspx");
        }
    }
}